﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Exam.Core.DBUtility;
using System.Data.OleDb;

namespace Exam.Core.DAL {
    public class Student {

        public List<Exam.Core.Model.Student> getStudentListByPage(int pagesize, int page, string departmentid, string keyword) {
            List<Exam.Core.Model.Student> list = new List<Exam.Core.Model.Student>();
            StringBuilder sb = new StringBuilder();
            if (departmentid.Trim() != "")
                sb.Append(" DepartmentID in(" + departmentid + ")");
            if (keyword.Trim() != "")
                sb.Append(" and (StudentName like '%" + keyword.Trim() + "%' OR StudentNumber like '%" + keyword.Trim() + "%')");
            sb.Append(" order by StudentID desc");
            StringBuilder SQL = new StringBuilder();
            SQL.Append("SELECT TOP " + pagesize + "  *  FROM Student ");
            if (page <= 0) {
                SQL.Append(" WHERE ");
                SQL.Append(sb.ToString());
            }
            else {
                SQL.Append(" WHERE StudentID NOT IN ( ");
                SQL.Append(" SELECT TOP " + pagesize * page + " StudentID FROM Student WHERE ");
                SQL.Append(sb.ToString() + " ) ");
                SQL.Append(" AND ");
                SQL.Append(sb.ToString());
            }
            DataTable dt = DbHelperOleDb.Query(SQL.ToString()).Tables[0];
            foreach (DataRow dr in dt.Rows) {
                Exam.Core.Model.Student obj = new Exam.Core.Model.Student();
                obj.StudentID = dr["StudentID"].ToString().Trim();
                obj.StudentNumber = dr["StudentNumber"].ToString().Trim();
                obj.StudentName = dr["StudentName"].ToString().Trim();
                obj.StudentPassword = dr["StudentPassword"].ToString().Trim();
                obj.StudentEmail = dr["StudentEmail"].ToString().Trim();
                obj.DepartmentID = dr["DepartmentID"].ToString().Trim();
                list.Add(obj);
            }
            return list;
        }

        public int getSize(string departmentid, string keyword) {
            StringBuilder sb = new StringBuilder();
            if (departmentid.Trim() != "")
                sb.Append(" DepartmentID in(" + departmentid + ")");
            if (keyword.Trim() != "")
                sb.Append(" and (StudentName like '%" + keyword.Trim() + "%' OR StudentNumber like '%" + keyword.Trim() + "%')");


            StringBuilder SQL = new StringBuilder();
            SQL.Append("SELECT Count(*)  FROM Student WHERE " + sb.ToString());

            object obj = DbHelperOleDb.GetSingle(SQL.ToString());
            return int.Parse(obj.ToString());
        }

        public List<Exam.Core.Model.Student> getStudentList() {
            List<Exam.Core.Model.Student> list = new List<Exam.Core.Model.Student>();
            StringBuilder SQL = new StringBuilder();
            SQL.Append("SELECT *  FROM Student ");
            DataTable dt = DbHelperOleDb.Query(SQL.ToString()).Tables[0];
            foreach (DataRow dr in dt.Rows) {
                Exam.Core.Model.Student obj = new Exam.Core.Model.Student();
                obj.StudentID = dr["StudentID"].ToString().Trim();
                obj.StudentNumber = dr["StudentNumber"].ToString().Trim();
                obj.StudentName = dr["StudentName"].ToString().Trim();
                obj.StudentPassword = dr["StudentPassword"].ToString().Trim();
                obj.DepartmentID = dr["DepartmentID"].ToString().Trim();
                list.Add(obj);
            }
            return list;
        }

        public List<Exam.Core.Model.Student> getStudentListByDeptId(int deptid) {
            List<Exam.Core.Model.Student> list = new List<Exam.Core.Model.Student>();
            StringBuilder SQL = new StringBuilder();
            SQL.Append("SELECT *  FROM Student WHERE DepartmentID=" + deptid);
            DataTable dt = DbHelperOleDb.Query(SQL.ToString()).Tables[0];
            foreach (DataRow dr in dt.Rows) {
                Exam.Core.Model.Student obj = new Exam.Core.Model.Student();
                obj.StudentID = dr["StudentID"].ToString().Trim();
                obj.StudentNumber = dr["StudentNumber"].ToString().Trim();
                obj.StudentName = dr["StudentName"].ToString().Trim();
                obj.StudentPassword = dr["StudentPassword"].ToString().Trim();
                obj.DepartmentID = dr["DepartmentID"].ToString().Trim();
                list.Add(obj);
            }
            return list;
        }

        public bool Add(string stuNum, string stuName, string stuPas, string stuEmail, string stuDept) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO Student(");
            strSql.Append("StudentNumber,StudentName,StudentPassword,StudentEmail,DepartmentID)");
            strSql.Append(" VALUES (");
            strSql.Append("@StudentNumber,@StudentName,@StudentPassword,@StudentEmail,@DepartmentID)");
            OleDbParameter[] parameters = {
					new OleDbParameter("@StudentNumber", OleDbType.VarChar,50),
					new OleDbParameter("@StudentName", OleDbType.VarChar,50),
					new OleDbParameter("@StudentPassword", OleDbType.VarChar,50),
                    new OleDbParameter("@StudentEmail", OleDbType.VarChar,50),
					new OleDbParameter("@DepartmentID", OleDbType.Integer,4)};
            parameters[0].Value = stuNum;
            parameters[1].Value = stuName;
            parameters[2].Value = stuPas;
            parameters[3].Value = stuEmail;
            parameters[4].Value = stuDept;
            bool result = false;
            int rows = DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0) {
                result = true;
            }
            return result;
            
        }
        //删除考生 同时删除考生试卷表 以及考生成绩表
        public bool Del(string list) {
            bool result = false;
            string SQL1 = "DELETE FROM StudentPaper WHERE StudentID in(" + list + ")";
            string SQL = "DELETE FROM Student WHERE StudentID in(" + list + ")";

            try {
                DbHelperOleDb.ExecuteSql(SQL1);
                DbHelperOleDb.ExecuteSql(SQL);
                result = true;
            }
            catch{
                result = false;
            }

            return result;
        }

        public bool Edit(string stuId, string stuNum, string stuName, string stuPas, string stuEmail, string stuDept) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE Student SET ");
            strSql.Append("StudentNumber=@StudentNumber,");
            strSql.Append("StudentName=@StudentName,");
            strSql.Append("StudentPassword=@StudentPassword,");
            strSql.Append("StudentEmail=@StudentEmail,");
            strSql.Append("DepartmentID=@DepartmentID");
            strSql.Append(" WHERE StudentID=@StudentID ");
            OleDbParameter[] parameters = {
					new OleDbParameter("@StudentNumber", OleDbType.VarChar,50),
					new OleDbParameter("@StudentName", OleDbType.VarChar,50),
					new OleDbParameter("@StudentPassword", OleDbType.VarChar,50),
					new OleDbParameter("@StudentEmail", OleDbType.VarChar,50),
					new OleDbParameter("@DepartmentID", OleDbType.Integer,4),
                    new OleDbParameter("@StudentID", OleDbType.Integer,4)
                };
            parameters[0].Value = stuNum.Trim();
            parameters[1].Value = stuName.Trim();
            parameters[2].Value = stuPas.Trim();
            parameters[3].Value = stuEmail.Trim();
            parameters[4].Value = stuDept.Trim();
            parameters[5].Value = stuId.Trim();
            bool result = false;
            int rows = DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0) {
                result = true;
            }
            return result;
        }

        public Exam.Core.Model.Student getStudent(int userId) {
            string SQL = "SELECT *  FROM Student WHERE StudentID=" + userId;
            Exam.Core.Model.Student obj = new Exam.Core.Model.Student();
            using (OleDbDataReader dr = DbHelperOleDb.ExecuteReader(SQL)) {
                while (dr.Read()) {
                    obj.StudentID = dr.GetInt32(0).ToString().Trim();
                    obj.StudentNumber = dr.GetString(1).Trim();
                    obj.StudentName = dr.GetString(2).Trim();
                    obj.StudentPassword = dr.GetString(3).Trim();
                    obj.StudentEmail = dr.GetValue(4).ToString().Trim();
                    obj.DepartmentID = dr.GetInt32(5).ToString().Trim();
                }
            }
            return obj;
        }

        public Exam.Core.Model.Student StudentByNumber(string uname) {
            string SQL = "SELECT * FROM Student WHERE StudentNumber=@StudentNumber";
            OleDbParameter[] parameters = {
					new OleDbParameter("@StudentNumber", OleDbType.VarChar,100)};
            parameters[0].Value = uname;
            Exam.Core.Model.Student obj = new Exam.Core.Model.Student();
            using (OleDbDataReader dr = DbHelperOleDb.ExecuteReader(SQL,parameters)) {
                while (dr.Read()) {
                    obj.StudentID = dr.GetInt32(0).ToString().Trim();
                    obj.StudentNumber = dr.GetString(1).Trim();
                    obj.StudentName = dr.GetString(2).Trim();
                    obj.StudentPassword = dr.GetString(3).Trim();
                    obj.StudentEmail = dr.GetValue(4).ToString().Trim();
                    obj.DepartmentID = dr.GetInt32(5).ToString().Trim();
                }
            }
            return obj;
        }

        public Exam.Core.Model.Student StudentById(int id) {
            string SQL = "SELECT * FROM Student WHERE StudentID=@StudentID";
            OleDbParameter[] parameters = {
					new OleDbParameter("@StudentID", OleDbType.Integer,4)};
            parameters[0].Value = id;
            Exam.Core.Model.Student obj = new Exam.Core.Model.Student();
            using (OleDbDataReader dr = DbHelperOleDb.ExecuteReader(SQL, parameters)) {
                while (dr.Read()) {
                    obj.StudentID = dr.GetInt32(0).ToString().Trim();
                    obj.StudentNumber = dr.GetString(1).Trim();
                    obj.StudentName = dr.GetString(2).Trim();
                    obj.StudentPassword = dr.GetString(3).Trim();
                    obj.StudentEmail = dr.GetValue(4).ToString().Trim();
                    obj.DepartmentID = dr.GetInt32(5).ToString().Trim();
                }
            }
            return obj;
        }


    }
}
